--    Author    : ChenErHao
--    Name      : DM.T_MTH_LOAN_STRUCTURE.HQL
--    Functions : 构成-贷款结构分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_LOAN_STRUCTURE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#')

SELECT
    TT.ORG_ID,
    TT.CUSTOMER_TYPE,
    TT.PRODUCT_CATEGORY,
    TT.LOAN_PERIOD, 
    TT.LPIC, 
    TT.LOANS_ACTUALLY,  
    TT.ENTERPRISE_SCALE, 
    TT.ENTERPRISE_INVESTOR, 
    TT.GUARANTY_STYLE, 
    TT.LOAN_STATUS, 
    '' LOAN_QUALITY,
    TT.AREA_NO_ID,
    nvl(SUM(LOAN_BALANCE),0)         LOAN_BALANCE, 
    nvl(SUM(LAST_MTH_LOAN_BALANCE),0)      LAST_MTH_LOAN_BALANCE, 
    nvl(SUM(LAST_YEAR_LOAN_BALANCE),0)      LAST_YEAR_LOAN_BALANCE, 
    nvl(SUM(YEAR_END_LOAN_BALANCE),0)     YEAR_END_LOAN_BALANCE, 
    nvl(SUM(LAST_YEAR_END_LOAN_BALANCE),0)    LAST_YEAR_END_LOAN_BALANCE, 
    nvl(SUM(M_LOAN_BALANCE_WEIGHT_AMT),0)      M_LOAN_BALANCE_WEIGHT_AMT, 
    nvl(SUM(LOAN_AMOUNT),0)   LOAN_AMOUNT, 
    nvl(SUM(LAST_MTH_LOAN_AMOUNT),0)   LAST_MTH_LOAN_AMOUNT, 
    nvl(SUM(LAST_YEAR_LOAN_AMOUNT),0) LAST_YEAR_LOAN_AMOUNT, 
    nvl(SUM(M_LOAN_AMOUNT_WEIGHT_AMT),0)    M_LOAN_AMOUNT_WEIGHT_AMT, 
    nvl(SUM(Y_LOAN_AMOUNT),0)  Y_LOAN_AMOUNT, 
    nvl(SUM(Y_LOAN_AMOUNT_WEIGHT_AMT),0)  Y_LOAN_AMOUNT_WEIGHT_AMT, 
    nvl(SUM(LY_LOAN_AMOUNT),0)  LY_LOAN_AMOUNT 
FROM
    (SELECT
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD, 
    T1.LPIC, 
    T1.LOANS_ACTUALLY,  
    T1.ENTERPRISE_SCALE, 
    T1.ENTERPRISE_INVESTOR, 
    T1.GUARANTY_STYLE, 
    T1.LOAN_STATUS, 
    '' LOAN_QUALITY,
    sum(case when DATA_DATE='#V_DATA_DATE#' then T1.LOAN_BALANCE end ) LOAN_BALANCE, 
    sum(case when DATA_DATE='#V_LAST_1M_END_DATE#' then T1.LOAN_BALANCE end) LAST_MTH_LOAN_BALANCE, 
    sum(case when DATA_DATE='#V_LAST_12M_END_DATE#' then T1.LOAN_BALANCE end) LAST_YEAR_LOAN_BALANCE, 
    sum(case when DATA_DATE='#V_DATA_DATE#' then T1.LOAN_BALANCE end) YEAR_END_LOAN_BALANCE, 
    sum(case when DATA_DATE='#V_LAST_1Y_END_DAY#' then T1.LOAN_BALANCE end)  LAST_YEAR_END_LOAN_BALANCE, 
    sum(case when DATA_DATE='#V_DATA_DATE#' then T1.WEIGHT_AMT end) M_LOAN_BALANCE_WEIGHT_AMT, 
    0 LOAN_AMOUNT, 
    0 LAST_MTH_LOAN_AMOUNT, 
    0 LAST_YEAR_LOAN_AMOUNT, 
    0 M_LOAN_AMOUNT_WEIGHT_AMT, 
    0 Y_LOAN_AMOUNT, 
    0  Y_LOAN_AMOUNT_WEIGHT_AMT, 
    0 LY_LOAN_AMOUNT
FROM
    (
        SELECT
            DATA_DATE,
            BELONG_AREA_CODE        AREA_NO_ID,             
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            CUSTOMER_TYPE         CUSTOMER_TYPE,  
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,  
            LOANS_PERIOD           LOAN_PERIOD, 
            NVL(LPIC_SUB,'9999') LPIC, 
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY, 
            NVL(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE, 
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR, 
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE, 
            NVL(LOAN_STATUS,'9999')            LOAN_STATUS, 
            SUM(LOAN_BALANCE)      LOAN_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
            (SELECT DATA_DATE,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
            CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.LOAB
        WHERE
            DATA_DATE  IN( '#V_DATA_DATE#'  ,'#V_LAST_1M_END_DATE#' ,  '#V_LAST_12M_END_DATE#' ,'#V_LAST_1Y_END_DAY#') ) A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end= B.ORG_ID  
        LEFT JOIN DMCODE.t_loan_pro_type C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.LOAN_PRO_ID  
        LEFT JOIN DMCODE.t_loans_actually G ON CASE WHEN a.LOANS_ACTUALLY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.LOANS_ACTUALLY end=G.loan_actually_id   
        LEFT JOIN DMCODE.t_investor_finicl_type_trans E ON CASE WHEN a.ENTERPRISE_INVESTOR IS NULL THEN CONCAT('org_code_hive',RAND()) else A.ENTERPRISE_INVESTOR end= E.investor_finicl_id  
        LEFT JOIN DMCODE.t_loan_insur_type F ON CASE WHEN a.GUARANTY_STYLE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.GUARANTY_STYLE end=F.loan_insur_id   
        GROUP BY
            DATA_DATE , A.BELONG_AREA_CODE , B.ORG_LVL_ID_2   ,
            CUSTOMER_TYPE  ,  C.loan_pro_id_1  ,  
            LOANS_PERIOD  ,  LPIC_SUB , G.loan_actually_id_1 ,
            ENTERPRISE_SCALE , E.investor_finicl_id_2   , F.loan_insur_id_1   ,
            LOAN_STATUS  ) T1 GROUP BY 
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD,--贷款期限,
    T1.LPIC,---企业分行业,
    T1.LOANS_ACTUALLY, -- 贷款实际投向,
    T1.ENTERPRISE_SCALE,--企业规模,
    T1.ENTERPRISE_INVESTOR,--企业出资人经济成分,
    T1.GUARANTY_STYLE,---担保方式,
    T1.LOAN_STATUS 
            
        UNION ALL
        
      SELECT
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD,--贷款期限,
    T1.LPIC,---企业分行业,
    T1.LOANS_ACTUALLY,--- 贷款实际投向,
    T1.ENTERPRISE_SCALE,--企业规模,
    T1.ENTERPRISE_INVESTOR,--企业出资人经济成分,
    T1.GUARANTY_STYLE,---担保方式,
    T1.LOAN_STATUS,--贷款状态,
    '' LOAN_QUALITY,
    0 LOAN_BALANCE,
    0 LAST_MTH_LOAN_BALANCE,
    0 LAST_YEAR_LOAN_BALANCE,
    0 YEAR_END_LOAN_BALANCE,
    0 LAST_YEAR_END_LOAN_BALANCE,
    0  M_LOAN_BALANCE_WEIGHT_AMT,
    sum(case when DATA_DATE='#V_DATA_DATE#' then T1.LOAN_AMOUNT end ) LOAN_AMOUNT,--当月发生额
    sum(case when DATA_DATE='#V_LAST_1M_END_DATE#' then T1.LOAN_AMOUNT end ) LAST_MTH_LOAN_AMOUNT,--上月发生额
    sum(case when DATA_DATE='#V_LAST_12M_END_DATE#' then T1.LOAN_AMOUNT end ) LAST_YEAR_LOAN_AMOUNT,--去年同期发生额
    sum(case when DATA_DATE='#V_DATA_DATE#' then T1.WEIGHT_AMT end )  M_LOAN_AMOUNT_WEIGHT_AMT,--当月发生额加权金额
    0 Y_LOAN_AMOUNT,--当年发生额
    0 Y_LOAN_AMOUNT_WEIGHT_AMT,--当年发生额加权金额
    0  LY_LOAN_AMOUNT --去年发生额
FROM
    (
        SELECT
            DATA_DATE,
            BELONG_AREA_CODE      AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            LOANS_PERIOD           LOAN_PERIOD,--贷款期限,
            NVL(LPIC_SUB,'9999') LPIC,--企业分行业
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY,--行业投向
            NVL(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,--企业规模
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码 
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,-- 贷款担保方式一级编码
            NVL(LOAN_STATUS,'9999')            LOAN_STATUS,--贷款状态
            SUM(LOAN_OCCURRENCE)      LOAN_AMOUNT,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
            (SELECT  DATA_DATE,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
            CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_OCCURRENCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.LOAF 
        WHERE
            DATA_DATE  IN('#V_DATA_DATE#'  ,'#V_LAST_1M_END_DATE#' ,  '#V_LAST_12M_END_DATE#' ,'#V_LAST_1Y_END_DAY#')  
            --AND LOAN_GRANT_RECOVER='1' --20171128 modify 原来只计算了发放额
            ) A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL  B ON A.DATA_ORG_CODE = B.ORG_ID  --金融机构
        LEFT JOIN DMCODE.t_loan_pro_type C ON A.PRODUCT_CATEGORY = C.LOAN_PRO_ID --贷款产品类别 
        LEFT JOIN DMCODE.t_loans_actually G ON A.LOANS_ACTUALLY=G.loan_actually_id   --贷款行业投向
        LEFT JOIN DMCODE.t_investor_finicl_type_trans E ON A.ENTERPRISE_INVESTOR = E.investor_finicl_id --企业出资人经济成分
        LEFT JOIN DMCODE.t_loan_insur_type F ON A.GUARANTY_STYLE=F.loan_insur_id   -- 贷款担保方式
        GROUP BY
             DATA_DATE    , BELONG_AREA_CODE    , B.ORG_LVL_ID_2    ,
            CUSTOMER_TYPE  ,  C.loan_pro_id_1    ,  
            LOANS_PERIOD  ,  LPIC_SUB   , G.loan_actually_id_1  ,
            ENTERPRISE_SCALE , E.investor_finicl_id_2   , F.loan_insur_id_1   ,
            LOAN_STATUS   ) T1 GROUP BY 
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.LOAN_PERIOD,--贷款期限,
    T1.LPIC,---企业分行业,
    T1.LOANS_ACTUALLY, -- 贷款实际投向,
    T1.ENTERPRISE_SCALE,--企业规模,
    T1.ENTERPRISE_INVESTOR,--企业出资人经济成分,
    T1.GUARANTY_STYLE,---担保方式,
    T1.LOAN_STATUS 
    
  UNION ALL  
  
        SELECT
            BELONG_AREA_CODE          AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            LOANS_PERIOD           LOAN_PERIOD,--贷款期限,
           NVL(LPIC_SUB,'9999') LPIC,--企业分行业
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY,--行业投向
            NVL(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,--企业规模
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码 
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,-- 贷款担保方式一级编码
            nvl(LOAN_STATUS,'9999')            LOAN_STATUS,--贷款状态
    '' LOAN_QUALITY,    
    0 LOAN_BALANCE,
    0 LAST_MTH_LOAN_BALANCE,
    0 LAST_YEAR_LOAN_BALANCE,
    0 YEAR_END_LOAN_BALANCE,
    0 LAST_YEAR_END_LOAN_BALANCE,
    0  M_LOAN_BALANCE_WEIGHT_AMT,
    0 LOAN_AMOUNT,--当月发生额
    0 LAST_MTH_LOAN_AMOUNT,--上月发生额
    0 LAST_YEAR_LOAN_AMOUNT,--去年同期发生额
    0  M_LOAN_AMOUNT_WEIGHT_AMT,--当月发生额加权金额
    sum(LOAN_OCCURRENCE)  Y_LOAN_AMOUNT,--当年发生额
    sum(LOAN_OCCURRENCE_LEVEL)   Y_LOAN_AMOUNT_WEIGHT_AMT,--当年发生额加权金额
    0  LY_LOAN_AMOUNT --去年发生额 
        FROM
            (SELECT  DATA_DATE,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,
           GUARANTY_STYLE,CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_OCCURRENCE,LOAN_OCCURRENCE_LEVEL
           FROM EDW.LOAF 
        WHERE
            DATA_DATE between '#V_YEAR_FIRST_DAY#'  and  '#V_DATA_DATE#'    
            )A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL  B ON A.DATA_ORG_CODE = B.ORG_ID  --金融机构
        LEFT JOIN DMCODE.t_loan_pro_type C ON A.PRODUCT_CATEGORY = C.LOAN_PRO_ID --贷款产品类别 
        LEFT JOIN DMCODE.t_loans_actually G ON A.LOANS_ACTUALLY=G.loan_actually_id   --贷款行业投向
        LEFT JOIN DMCODE.t_investor_finicl_type_trans E ON A.ENTERPRISE_INVESTOR = E.investor_finicl_id --企业出资人经济成分
        LEFT JOIN DMCODE.t_loan_insur_type F ON A.GUARANTY_STYLE=F.loan_insur_id   -- 贷款担保方式
        GROUP BY  A.BELONG_AREA_CODE    , B.ORG_LVL_ID_2    ,
            CUSTOMER_TYPE  ,  C.loan_pro_id_1    ,  
            LOANS_PERIOD  ,  LPIC_SUB   , G.loan_actually_id_1  ,
            ENTERPRISE_SCALE , E.investor_finicl_id_2   , F.loan_insur_id_1   ,
            LOAN_STATUS  
            
             union all
             
              SELECT
            BELONG_AREA_CODE          AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            LOANS_PERIOD           LOAN_PERIOD,--贷款期限,
            NVL(LPIC_SUB,'9999')  LPIC,--企业分行业
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY,--行业投向
            NVL(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,--企业规模
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码 
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,-- 贷款担保方式一级编码
            nvl(LOAN_STATUS,'9999')            LOAN_STATUS,--贷款状态
    '' LOAN_QUALITY,    
    0 LOAN_BALANCE,
    0 LAST_MTH_LOAN_BALANCE,
    0 LAST_YEAR_LOAN_BALANCE,
    0 YEAR_END_LOAN_BALANCE,
    0 LAST_YEAR_END_LOAN_BALANCE,
    0  M_LOAN_BALANCE_WEIGHT_AMT,
    0 LOAN_AMOUNT,--当月发生额
    0 LAST_MTH_LOAN_AMOUNT,--上月发生额
    0 LAST_YEAR_LOAN_AMOUNT,--去年同期发生额
    0  M_LOAN_AMOUNT_WEIGHT_AMT,--当月发生额加权金额
    0  Y_LOAN_AMOUNT,--当年发生额
    0  Y_LOAN_AMOUNT_WEIGHT_AMT,--当年发生额加权金额
    sum(LOAN_OCCURRENCE)  LY_LOAN_AMOUNT --去年发生额 
        FROM
            (SELECT BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
            DATA_DATE,CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_OCCURRENCE
            FROM EDW.LOAF 
        WHERE
            DATA_DATE between '#V_LAST_1Y_FIRST_DAY#'  and  '#V_LAST_1Y_END_DAY#'    
         )A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL  B ON A.DATA_ORG_CODE = B.ORG_ID  --金融机构
        LEFT JOIN DMCODE.t_loan_pro_type C ON A.PRODUCT_CATEGORY = C.LOAN_PRO_ID --贷款产品类别 
        LEFT JOIN DMCODE.t_loans_actually G ON A.LOANS_ACTUALLY=G.loan_actually_id   --贷款行业投向
        LEFT JOIN DMCODE.t_investor_finicl_type_trans E ON A.ENTERPRISE_INVESTOR = E.investor_finicl_id --企业出资人经济成分
        LEFT JOIN DMCODE.t_loan_insur_type F ON A.GUARANTY_STYLE=F.loan_insur_id   -- 贷款担保方式
        GROUP BY  BELONG_AREA_CODE    , B.ORG_LVL_ID_2    ,
            CUSTOMER_TYPE  ,  C.loan_pro_id_1    ,  
            LOANS_PERIOD  ,  LPIC_SUB   , G.loan_actually_id_1  ,
            ENTERPRISE_SCALE , E.investor_finicl_id_2   , F.loan_insur_id_1   ,
            LOAN_STATUS  
               )TT
    GROUP BY
    TT.ORG_ID,
    TT.CUSTOMER_TYPE,
    TT.PRODUCT_CATEGORY,
    TT.LOAN_PERIOD,--贷款期限,
    TT.LPIC,---企业分行业,
    TT.LOANS_ACTUALLY, -- 贷款实际投向,
    TT.ENTERPRISE_SCALE,--企业规模,
    TT.ENTERPRISE_INVESTOR,--企业出资人经济成分,
    TT.GUARANTY_STYLE,---担保方式,
    TT.LOAN_STATUS,
    TT.AREA_NO_ID
;